package com.work.utils;

import com.work.bean.*;

/**
 * Created by Administrator on 2017/9/29.
 */
public class SQLUtil {

    public static String findByStuUsernameSql(String username) {
        return "select * from student where username='" + username + "'";
    }

    public static String findByTeacherUsernameSql(String username) {
        return "select * from teacher where username='" + username + "'";
    }

    public static String insertStudentSql(Student stu) {
        return "insert into student(username,password) values('" + stu.getUsername() + "','" + stu.getPassword() + "')";
    }

    public static String insertTeacherSql(Teacher teacher) {
        return "insert into teacher(username,password) values('" + teacher.getUsername() + "','" + teacher.getPassword() + "')";
    }

    public static String loginStudent(Student student) {
        return "select * from student where username='" + student.getUsername() + "' and password='" + student.getPassword() + "'";
    }

    public static String loginTeacher(Teacher teacher) {
        return "select * from teacher where username='" + teacher.getUsername() + "' and password='" + teacher.getPassword() + "'";
    }


    public static String insertWorkSql(Work work) {

        return "insert into work(teacherId,downloadPath,readOnLinePath,workName,startTime,endTime,distribute,finishProcess) values(" + work.getTeacherId() + ",'" + work.getDownloadPath() + "','" + work.getReadOnLinePath() + "','" + work.getWorkName() + "','" + work.getStartTime() + "','" + work.getEndTime() + "',0,'0')";
    }

    public static String findByTeacherIdSql(int teacherId) {
        return "select * from work where teacherId=" + teacherId + " order by startTime desc";
    }

    public static String deleteByWorkId(String deleteId) {
        return "delete from work where id=" + deleteId;
    }

    public static String pageByTeacherId(int teacherId) {
        return "select * from class where teacherId=" + teacherId;
    }

    public static String deleteByClassId(String deleteId) {
        return "delete from class where id=" + deleteId;
    }

    public static String insertClass(ClassW classW) {
        return "insert into class(className,teacherId) values('" + classW.getClassName() + "'," + classW.getTeacherId() + ")";

    }

    public static String updateClassName(ClassW classW) {
        return "update class set className='" + classW.getClassName() + "' where id=" + classW.getId();
    }

    public static String findMyStudentSql(int teacherId) {

        return "select s.id,s.username,c.classname from student s,class c where s.classid=c.id and c.teacherid =" + teacherId;
    }

    public static String updateClassId(String id) {

        return "update student set classId='' where id=" + id;
    }

    public static String findStudentByClassIdSql(String id) {

        return "select * from student where classId=" + id;
    }

    public static String insertMiddleSql(Middle middle) {
        return "insert into middle(studentId,workId,finish) values(" + middle.getStudentId() + "," + middle.getWorkId() + ",0)";
    }

    public static String findByWorkIdSql(int workId) {
        return "select * from middle where workId=" + workId;
    }

    public static String findByFinishSql(int finish, int workId) {
        return "select * from middle where finish=" + finish + " and workId=" + workId;
    }

    public static String updateDistribute(int distribute, int workId) {
        return "update work set distribute=" + distribute + " where id=" + workId;
    }

    public static String updateFinishProcess(String process, int workID) {
        return "update work set finishProcess='" + process + "' where id=" + workID;
    }

    public static String findByFinishNSql(int finish, int workId) {
        return "select m.studentid,m.workid,m.studentsubmitpath,m.readonlinepath,s.username,m.filename,m.grade from middle m,student s where m.studentid=s.id and m.workid=" + workId + " and m.finish=" + finish;
    }

    public static String findMyClassByStuId(int studentId) {
        return "select s.id sid,s.username,c.id cid,c.classname from student s,class c where s.classid=c.id and s.id=" + studentId;
    }

    public static String findByStudentIdSql(int isFinish, int studentId) {
        return "select m.studentid,m.workid,m.studentsubmitpath,m.readonlinepath stuReadOnLinePath,m.filename,w.downloadpath teachDownloadPath,w.readonlinepath teachReadOnLinePath,w.workname,w.starttime,w.endtime from middle m,work w where m.workid=w.id and m.studentid=" + studentId + " and m.finish=" + isFinish;
    }

    public static String updateMiddle(Middle middle) {

        return "update middle set STUDENTSUBMITPATH='" + middle.getStudentSubmitPath() + "',FINISH=1,READONLINEPATH='" + middle.getReadOnlinePath() + "',FILENAME='" + middle.getFileName() + "' where STUDENTID="+middle.getStudentId()+" and WORKID="+middle.getWorkId();
    }

    public static String updateGrade(Middle middle) {

        return "update middle set grade=" + middle.getGrade() + " where studentId=" + middle.getStudentId()+" and workId="+middle.getWorkId();
    }

    public static String updateStudent(Student student) {

        return "update student set CLASSID=" + student.getClassId() + " where id=" + student.getId();
    }

    public static String findByClassIdSql(int classId) {
        return "select * from class where id="+classId;
    }
}
